USE ASLB 
GO
IF (OBJECTPROPERTY ( OBJECT_ID('MonitorASPerformance'), 'IsProcedure' ) IS NOT NULL)
BEGIN
   DROP PROC MonitorASPerformance
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO


CREATE PROC [dbo].[MonitorASPerformance] 
       
AS
/***********************************************************************************
** Name      : MonitorASPerformance
**
** File Name : MonitorASPerformance.sql
**
** Desc      : 
**
** Called By : 
**
** Calls     : 
**
** Returns   :
** Author    : jocaplan
**
** Date      : 03/30/2009
**
** Parameters: 
                

** Usage     :
               

************************************************************************************
** Change History
************************************************************************************
** Date        Author     Description
** ----------  ---------  ----------------------------------------------------------
**********************************************************************************/

SET NOCOUNT ON

DECLARE
   @ServerName varchar(50)
   ,@RC INT
   ,@Counter INT
   ,@NumOfRows INT
   ,@PerfCounter float
   ,@ErrorDesc varchar(MAX)
   ,@RoundRobin int
   ,@RoundRobinThreshold int
   

-- Figure out if we are using round robin
SELECT @RoundRobin = ParamNumber
FROM
   Parameters
WHERE
   ParamName = 'UseRoundRobin'
   
SELECT @RoundRobinThreshold = ParamNumber
FROM
   Parameters
WHERE
   ParamName = 'RoundRobinThreshold'
   
IF @RoundRobin = 0
BEGIN
   -- Get a list of all of the active servers
   SELECT
      Name as ServerName
      ,0 as Status
   INTO #ActiveServers
   FROM
      [Server] (nolock)
   WHERE
      Disabled = 0
      AND Offline = 0
      
   SELECT @NumOfRows = COUNT(*) FROM #ActiveServers
   SELECT @Counter = 0

   WHILE @Counter < @NumOfRows
   BEGIN

      SELECT TOP 1
         @ServerName = ServerName
      FROM
         #ActiveServers
      WHERE
         [Status] = 0
         
      EXEC @RC = CheckServerPerformance @ServerName, @PerfCounter = @PerfCounter out, @ErrorDesc = @ErrorDesc out
      
      IF @RC = 0
      BEGIN
      
         UPDATE [Server]
         SET
            [Load] = CASE WHEN @PerfCounter > @RoundRobinThreshold THEN @PerfCounter ELSE 0 END
            ,LoadUpdateDate = GETDATE()
         WHERE
            Name = @ServerName
      
      END
      
      
      
         
      UPDATE #ActiveServers
      SET Status = 1
      WHERE
         ServerName = @ServerName
         
      Select @Counter = @Counter+1



   END

END
ELSE
BEGIN

   UPDATE [Server]
   SET
      [Load] = 0
      ,LoadUpdateDate = GETDATE()


END